/**
 * user manage class,it contains user definition and new/modify/delete user role
 * last update by lynmison@126.com 2016.03.29
 **/
package com.fzrskj.cloud.free.sys.dao;

import com.fzrskj.cloud.free.core.tool.CEncryptTool;
import com.fzrskj.cloud.free.core.tool.CJsonTool;
import com.fzrskj.cloud.free.core.utils.CloudContextUtils;
import com.fzrskj.cloud.free.core.utils.CloudDbDrvUtils;
import com.fzrskj.cloud.free.sys.bean.User;
import com.fzrskj.cloud.free.user.utils.UserAccountUtils;
import com.winiis.component.common.utils.bean.ClassUtils;
import com.winiis.component.common.utils.db.DbUtils;
import ms.core.consts.SysErr;
import ms.core.db.IDbDrv;
import ms.core.object.CObject;
import ms.core.tool.DateTimeTool;
import ms.core.tool.StrTool;
import net.sf.json.JSONArray;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CUserDao extends CObject {
    //private declaration
    private int ACCOUNT_SEED = 999;
    IDbDrv db = null;

    //constructor function to initialize database
    public CUserDao() {
        db = CloudDbDrvUtils.getDbUtilsDrv();
    }

    //dispose function implements to free resource
    @Override
    public void dispose() {
        super.dispose();
        db.closeConnection();
        db = null;
    }

    //to calculate new administrator account id
    private int getAccountId() {
        String sql = "select max(accountId) from sys_user where accountId>=" + ACCOUNT_SEED;
        Integer ret = (Integer) db.queryFieldValue(sql, Integer.class, new Object[]{}, 1);
        if (ret == null) {
            return ACCOUNT_SEED + 1;
        }
        return ret.intValue() + 1;
    }

    //to add new user
    public int newUser(User user) {
        User tmp = queryUser(user.getAccount());
        if (tmp != null) {
            this.setErrCode(SysErr.USER_EXISTS);
            return 0;
        }
        //if (user.getAgentId() == null){
            user.setAgentId(CloudContextUtils.getAgentId());
        //}
        user.setState(0);
        user.setCreateTime(new Date());
        user.setAccountId(getAccountId());
        user.setRoles(String.format("[{\"id\":\"%s\"}]", user.getRoles()));
        String pass = CEncryptTool.encodePassword(user.getPassword());
        if (CloudContextUtils.getAgentId() > 0) {
            pass = UserAccountUtils.encodePassword(user.getPassword());
        }
        String strSql = "insert into sys_user(accountId,account,department,duty,roles,password,utype,nick,createTime," +
                "state,mobile,officeTel,groupCode,agentId) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] param = {user.getAccountId(), user.getAccount(), user.getDepartment(), user.getDuty(), user.getRoles(),
                pass, user.getUtype(), user.getNick(), user.getCreateTime(), user.getState(), user.getMobile(),
                user.getOfficeTel(), user.getGroupCode(), user.getAgentId()};
        int ret = db.executeSql(strSql, param);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        setErrCode(err);

        if (ret >= 0) {
            user.setId(DbUtils.lastId(this.db));
            ret = user.getId();
        }
        return ret;
    }

    //to modify user information
    public int modUser(User user) {
        user.setRoles(String.format("[{\"id\":\"%s\"}]", user.getRoles()));
        String sql = "update sys_user set department=?, duty=?, nick=?, mobile=?, officeTel=?, utype=?, roles=?," +
                " groupCode=? where id=? and agentId=?";
        Object[] params = {user.getDepartment(), user.getDuty(), user.getNick(), user.getMobile(),
                user.getOfficeTel(), user.getUtype(), user.getRoles(), user.getGroupCode(), user.getId(), CloudContextUtils.getAgentId()};
        int ret = db.executeSql(sql, params);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        setErrCode(err);
        return ret;
    }

    //to update user last visit info,it contains visitor ip and last visit time
    public void updateVisit(int uid, String visitIp) {
        if (StrTool.isBlankStr(visitIp)) {
            visitIp = "unknown ip";
        }

        String visitTime = DateTimeTool.dateToDateTimeString(new Date());
        String sql = "update sys_user set visitIp=?,visitTime=? where id=? and agentId=?";
        Object[] params = {visitIp, visitTime, uid, CloudContextUtils.getAgentId()};
        db.executeSql(sql, params);
        this.setErrCode(db.getErrCode());
    }

    //modify user's password by record id
    public int setUserPassword(int rid, String newPass) {
        String sql = "update sys_user set pass=? where id=? and agentId=?";
        Object[] params = {newPass, rid, CloudContextUtils.getAgentId()};
        int ret = db.executeSql(sql, params);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        setErrCode(err, codeOfMsg(err));
        return ret;
    }

    //Modify the user's status by recording the identity
    public int setUserStatus(int rid, int state) {
        String sql = "update sys_user set state=? where id=? and agentId=?";
        Object[] params = {state, rid, CloudContextUtils.getAgentId()};
        int ret = db.executeSql(sql, params);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err, codeOfMsg(err));
        return ret;
    }

    //to modify user role
    public int setUserRole(String account, String roles) {
        String sql = "update sys_user set roles=? where account=? and agentId=?";
        Object[] params = {roles, account, CloudContextUtils.getAgentId()};
        int ret = db.executeSql(sql, params);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err, codeOfMsg(err));
        return ret;
    }

    //to remove one user's role,remark: user's role format:[{"id":"xxx"},{"id","xxx"}]
    private boolean removeUserRole(User user, String roleName) {
        JSONArray ary = CJsonTool.getJsonArray(user.getRoles());
        if (ary == null) {
            return false;
        }

        for (int i = 0; i < ary.size(); i++) {
            Object obj = CJsonTool.getJsonArrayValue(ary, i, "id");
            if ((obj != null) && (obj.toString().toLowerCase().equals(roleName.toLowerCase()))) {
                ary.remove(i);
                setUserRole(user.getAccount(), obj.toString());
                return true;
            }
        }
        return false;
    }

    /**
     * to remove all user's role by indicating role name
     *
     * @param roleName role name to be remove from user account
     * @return account effected
     * @author lms 2016.08.08
     */
    public int delUsersRole(String roleName) {
        int ret = 0;
        List<User> users = queryUserList();
        if (users == null) {
            return ret;
        }

        for (User user : users) {
            if (removeUserRole(user, roleName)) {
                ret++;
            }
        }
        return ret;
    }

    //delete user by id
    public int delUser(int id) {
        String strSql = String.format("delete from sys_user where id=%d and agentId=%d", id, CloudContextUtils.getAgentId());
        int ret = db.executeSql(strSql);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return ret;
    }

    //delete user by account
    public int delUser(String account) {
        String strSql = String.format("delete from sys_user where account='%s' and agentId=%d", account, CloudContextUtils.getAgentId());
        int ret = db.executeSql(strSql);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return ret;
    }

    //query user information
    public List<User> queryUserList(int page, int pageSize) {
        if (page <= 1) {
            page = 1;
        }
        String strSql = String.format("select * from sys_user where agentId=%d limit %d,%d", CloudContextUtils.getAgentId(), (page - 1) * pageSize, pageSize);
        List<User> ret = db.queryForBeans(strSql, User.class);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return ret;
    }

    //query all user
    public List<User> queryUserList() {
        String strSql = String.format("select * from sys_user where agentId=?");
        List<User> ret = db.queryForBeans(strSql, User.class,new Object[]{CloudContextUtils.getAgentId()});
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return ret;
    }

    //to get user num
    public int queryUserNum() {
        Map<String, Object> cdt =  new HashMap<>();
        cdt.put("agentId", CloudContextUtils.getAgentId());
//        int ret = DbUtils.getTableRows(this.db, "sys_user",cdt);
        int ret = db.queryRecordCount("sys_user", cdt);
        String err = this.db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(this.db.getMsg());
        }
        this.setErrCode(err);
        return ret;
    }

    //query user information by indicating account
    public User queryUser(String account) {
        String strSql = "select * from sys_user where account=? and agentId=?";
        Object[] params = {account, CloudContextUtils.getAgentId()};
        User ret = db.queryForBean(strSql, User.class, params);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return ret;
    }

    //query user information by indicating record id
    public User queryUser(int id) {
        String strSql = "select * from sys_user where id=? and agentId=?";
        Object[] params = {id, CloudContextUtils.getAgentId()};
        User ret = db.queryForBean(strSql, User.class, params);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        setErrCode(err);
        return ret;
    }

    //query user functions, the functions set is like "[{"id":"fun1"},{"id":"fun2"}...]"
    //role's format: [{"id":"admin"},{"id":"seller"}...]
    public String queryUserFuncs(String roles) {
        if (StrTool.isBlankStr(roles)) {
            return "";
        }

        CRoleDao rd = new CRoleDao();
        String funs = rd.queryRoleFunc(roles);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        rd.dispose();
        return funs;
    }

    /**
     * 根据用户名以及密码查询后台对象
     *
     * @param userName
     * @param oldPassword
     * @return
     */
    public User queryPassword(String userName, String oldPassword) {
        String sql = "select * from sys_user where account=? and password=? and agentId=?";
        User queryPassword = db.queryForBean(sql, User.class, new Object[]{userName, oldPassword, CloudContextUtils.getAgentId()});
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return queryPassword;
    }

    /**
     * 根据用户名修改密码
     *
     * @param userName
     * @param newPassword
     * @return
     */
    public int uptadminpas(String userName, String newPassword) {
        String sql = "update sys_user set password=? where account=? and agentId=?";
        int ret = db.executeSql(sql, new Object[]{newPassword, userName, CloudContextUtils.getAgentId()});
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return ret;
    }

    /**
     * 自动分配客户（客户少优先）.
     *
     * @param groupCode
     * @return
     */
    public User getAllotUser(Integer groupCode) {
        String column = "crmId";
        if (groupCode == 3) {
            column = "csstId";
        }
        String sql = "SELECT a.*, (SELECT count(1) from user_account b where b." + column + "=a.accountId) as total " +
                " from sys_user a where a.groupCode=? and agentId=? order by total limit 1 ";

        User user = this.db.queryForBean(sql, User.class, new Object[]{groupCode, CloudContextUtils.getAgentId()});
        String err = this.db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(this.db.getMsg());
        }
        this.setErrCode(err);
        return user;
    }

    /**
     * 通过账号ID获取用户.
     *
     * @param accountId account id.
     * @return user.
     */
    public User getUser(Integer accountId) {
        String sql = "select * from sys_user where accountId=? and agentId=? limit 1";
        User user = this.db.queryForBean(sql, User.class, new Object[]{accountId, CloudContextUtils.getAgentId()});
        String err = this.db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(this.db.getMsg());
        }
        this.setErrCode(err);
        return user;
    }

    /**
     * 无代理隔离查询.
     * @param cdt 查询条件.
     * @return 查询结果.
     */
    public List<User> queryUserNoAgent(Map<String, Object> cdt) {
        Object[] params = new Object[cdt.size()];
        String sql = "select * from sys_user where 1=1";

        int i=0;
        for(Map.Entry<String, Object> c: cdt.entrySet()){
            sql = sql+" and "+c.getKey()+"=?";
            params[i] = c.getValue();
            i++;
        }

        List<User> userList = this.db.queryForBeans(sql, User.class, params);
        String err = this.db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(this.db.getMsg());
        }
        this.setErrCode(err);

        return userList;
    }

    public Integer modifyUserNotAgent(Map<String, Object> values, Map<String, Object> cdt) {
        int i = this.db.modifyRecord("sys_user", values, cdt);
        this.setErrCode(this.db.getErrCode());
        return i;
    }

    public Integer newUserNotAgent(User user) {
        user.setAccountId(this.getAccountId());
        Map userMap = ClassUtils.beanToMap(user);
        userMap.remove("funcs");
        userMap.remove("checkcode");
        userMap.remove("groupCode");
        Integer affect = this.db.insertRecord("sys_user", userMap);
        this.setErrCode(this.db.getErrCode());
        return affect;
    }

    public Integer copyAdminRight(Integer agentId) {
        String sql = "insert into sys_role (roleName, roleCName, funcs, remark, createTime, agentId) " +
                " select 'admin', 'admin', funcs, '管理员', now(), '" + String.valueOf(agentId) +
                "' as agentId from sys_role where roleName='agent' and agentId=0 ";
        Integer affect = this.db.executeSql(sql);
        this.setErrCode(this.db.getErrCode());
        return affect;
    }

    public List<User> qryCrmUserList(){
        String strSql = "select account,nick,mobile,id from sys_user where  roles like '%crm%'";
        List<User> ret = db.queryForBeans(strSql, User.class,new Object[]{});
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return ret;
    }
}
